package com.joysuch.util;

import com.joysuch.annotation.IgnoreSelect;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;

@Slf4j
public class JdbcH2Util {
    private static final Connection connection;

    static {
//        log.info("进入JdbcH2Util静态代码块-{}", LocalDateTime.now());
        String url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
        String user = "";
        String password = "";
        try {
            Class.forName("org.h2.Driver");
            connection = DriverManager.getConnection(url, user, password);
            log.info("创建连接完成-{}", LocalDateTime.now());
        } catch (SQLException e) {
            log.error("创建H2数据库连接发生异常，异常信息-{}---{}",e, LocalDateTime.now());
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public static void createTable(Class<?> T) {
//        log.info("createTable(T, null); 方法执行-{}", LocalDateTime.now());
        createTable(T, null);
    }

    public static void createTable(Class<?> T, String name) {
        log.info("开始建表({},{})-{}", T, name, LocalDateTime.now());
        String createTableSql = getCreateTableSql(T, name);
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(createTableSql);
            statement.execute();
            log.info("建表结束-{}", LocalDateTime.now());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(null, statement);
        }

    }

    public static <T> List<T> query(Class<T> clazz, String sql) {
        PreparedStatement preparedStatement = null;
        List<T> list = new ArrayList<>();
        Field[] fields = clazz.getDeclaredFields();
        T entity = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet != null) {
                while (resultSet.next()) {
                    entity = clazz.newInstance();
                    for (Field field : fields) {
                        if (Objects.nonNull(field.getAnnotation(IgnoreSelect.class))) {
                            continue;
                        }
                        field.setAccessible(true);
                        try {
                            field.set(entity, resultSet.getObject(field.getName()));
                        } catch (SQLException e) {

                        }
                    }
                    list.add(entity);
                }
            }
        }
        catch (InstantiationException | IllegalAccessException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(resultSet, preparedStatement);
        }
        return list;
    }

    public static <T> boolean insert(T data, Class<T> clazz) {
        return insert(data, clazz, null);
//        boolean flag = true;
//        String tableName = clazz.getSimpleName();
//        Field[] fields = clazz.getDeclaredFields();
//        try {
//            StringBuilder insertSqlBuilder = new StringBuilder("INSERT INTO `" + tableName + "` (");
//            StringBuilder dataBuilder = new StringBuilder();
//            Map<Integer, Object> dataMap = new HashMap<>();
//            Integer index = 1;
//            for (Field field : fields) {
//                field.setAccessible(true);
//                dataMap.put(index, field.get(data));
//                insertSqlBuilder.append("`").append(field.getName()).append("`,");
//                dataBuilder.append("?,");
//                index++;
//            }
//            String insertSql = insertSqlBuilder.substring(0, insertSqlBuilder.length() - 1) + ") values (" + dataBuilder.substring(0, dataBuilder.length() - 1) + ")";
//            PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
//            for (int i = 1; i <= fields.length; i++) {
//                preparedStatement.setObject(i, dataMap.get(i));
//            }
//            preparedStatement.execute();
//
//        } catch (IllegalAccessException | SQLException e) {
//            flag = false;
//        }
//        return flag;
    }

    public static <T> boolean insert(T data, Class<T> clazz, String tableName) {
        boolean flag = true;
        if (Objects.isNull(tableName)) {
            tableName = clazz.getSimpleName();
        }
        Field[] fields = clazz.getDeclaredFields();
        try {
            StringBuilder insertSqlBuilder = new StringBuilder("INSERT INTO `" + tableName + "` (");
            StringBuilder dataBuilder = new StringBuilder();
            Map<Integer, Object> dataMap = new HashMap<>();
            Integer index = 1;
            for (Field field : fields) {
                field.setAccessible(true);
                dataMap.put(index, field.get(data));
                insertSqlBuilder.append("`").append(field.getName()).append("`,");
                dataBuilder.append("?,");
                index++;
            }
            String insertSql = insertSqlBuilder.substring(0, insertSqlBuilder.length() - 1) + ") values (" + dataBuilder.substring(0, dataBuilder.length() - 1) + ")";
            PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
            for (int i = 1; i <= fields.length; i++) {
                preparedStatement.setObject(i, dataMap.get(i));
            }
            preparedStatement.execute();

        } catch (IllegalAccessException | SQLException e) {
            flag = false;
        }
        return flag;
    }

    private static String getCreateTableSql(Class<?> clazz, String tableName) {
        Map<String, String> fieldTypeMap = getSqlFieldType();
        if (Objects.isNull(tableName)) {
            tableName = clazz.getSimpleName();
        }

        Field[] fields = clazz.getDeclaredFields();
        String sqlFieldType;
        StringBuilder createTableSqlBuilder = new StringBuilder("CREATE TABLE IF NOT EXISTS `" + tableName + "` (");
        for (Field field : fields) {
            sqlFieldType = null;
            String fieldName = field.getName();
            if (Objects.equals(fieldName, "id")) {
                sqlFieldType = "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY";
            } else {
                sqlFieldType = fieldTypeMap.get(field.getType().toString());
            }
            createTableSqlBuilder.append("`").append(fieldName).append("` ").append(sqlFieldType).append(",");
        }

        return createTableSqlBuilder.substring(0, createTableSqlBuilder.length() - 1) + ")";
    }

    private static Map<String, String> getSqlFieldType() {
        Map<String, String> fieldMap = new HashMap<>();
        fieldMap.put("class java.lang.Integer", "int(11)");
        fieldMap.put("int", "int(11) DEFAULT NULL");
        fieldMap.put("class java.lang.String", "varchar(255) DEFAULT NULL");
        fieldMap.put("class java.math.BigDecimal", "decimal(18,12) DEFAULT NULL");
        fieldMap.put("class java.lang.Long", "bigint");
        return fieldMap;
    }

    private static void close(ResultSet resultSet, Statement statement) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void closeConnection() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
